<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use DB;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class DbController extends Controller
{   //数据操作
    public function getDatabase()
    {
        //数据查询
        $res = DB::select('select * from user');
        //数据插入
        // $res = DB::insert('insert into user(username,password,sex) values(?,?,?)',['sansan','1221','男']);
        //数据修改
        // $res = DB::update('update user set age=? where id=? ',[123,45]);
        //数据删除
        $res = DB::delete('delete from  user where id=?',[17]);
        //一般语句
        // DB::statement('drop table money');
        dd($res);
    }
    //事务处理
    public function getShiwu(){
        //1.开启事务
        DB::beginTransaction();
        //2.需求
        $res1 = DB::update('update money set money = money-1000 where username="zhansan"');
        $res2 = DB::update('update money set money = money+1000 where username="lisi"');
        if($res1 && $res2){
            echo '转账成功';
            DB::commit();
        }else{
            echo '转账失败';
            DB::rollback();
        }
    }
    //查询构造器
    public function getTables(){
        //查询所有信息
        $res = DB::table('user')->get();
        //查询一条信息
        $res = DB::table('user')->where('id','=','26')->first();
        //查询指定字段
        $res = DB::table('user')->value('username');
        //查询一列信息
        $res = DB::table('user')->lists('id');
        //查询指定信息
        $res = DB::table('user')->select('username','id')->get();
        //插入一条信息
        $res = DB::table('user')->insert(['username'=>'zzz','password'=>'123']);
        //插入多条信息
        $arr = [
            ['username'=>'aaa','age'=>'123','sex'=>'男'],
            ['username'=>'bbb','age'=>'234','sex'=>'男'],
            ['username'=>'ccc','age'=>'456','sex'=>'女']

        ];
        $res = DB::table('user')->insert($arr);
        //返回插入的ID
        $res = DB::table('user')->insertGetId(['username'=>'ccc','age'=>'456','sex'=>'女']);
        //删除一条数据
        $res = DB::table('user')->where('id','=','26')->delete();
        //修改语句
        $res = DB::table('user')->where('id','=','27')->update(['password'=>'741']);
        //连贯操作
        $res = DB::table('user')->where('id','=','1')->orwhere('username','=','周小金')->get();
        $res = DB::table('user')->whereBetween('id',[30,50])->get();
        $res = DB::table('user')->whereIn('id',[35,36,37])->get();
        //排序
        $res = DB::table('user')->orderby('id','asc')->get();
        //分页
        $res = DB::table('user')->skip(10)->take(5)->get();
        //表连接
        // $res = DB::table('user as u')->join('money as d ','u.id','=','d.id')->select('u.id','u.username','u.password','u.sex','u.age','d.money','d.username')->get();
         $res = DB::table('user as u')->join('money as d','u.id','=','d.id')->select('u.id','u.username','u.sex','u.age','d.username','d.money')->get();
         //计算总条数
         $res = DB::table('user')->count();
         //计算最大值
         $res = DB::table('user')->max('id');
         //计算平均值
         $res = DB::table('user')->avg('id');
        dd($res);
    }
}
